package com.xyw.Dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.xyw.Dao.CommodityDao;
import com.xyw.Dao.UserDao;
import com.xyw.entity.Commodity;
import com.xyw.entity.Scenery;
import com.xyw.entity.User;
import com.xyw.util.JDBCUtil;

public class CommodityDaoImpl implements CommodityDao {
	JDBCUtil util = JDBCUtil.getInit();

	@Override
	public List<Commodity> query() {
		CachedRowSet rs = util.query("select * from xyw_commodity a inner join xyw_commodity u where a.user_id=u.user_id");
		List<Commodity> list = new ArrayList<Commodity>();
		try {
			while (rs.next()) {
				Commodity d = new Commodity();
				User u = new User();
				d.setUnitPrice(rs.getInt("Unit_Price"));
				d.setCommodityName(rs.getString("Commodity_name"));
				d.setCommodityStock(rs.getInt("Commodity_stock"));
				d.setCommodityPicture(rs.getString("Commodity_picture"));
				d.setProductInformation(rs.getString("Product_information"));
                d.setUserId(((UserDao) u).queryByID(rs.getInt("user_id")));
				list.add(d);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}

	@Override
	public Commodity add(Commodity c) {
		String sql = "insert into xyw_commodity(User_id,Unit_Price,Commodity_name,Commodity_stock,Commodity_picture,Product_information)values(?,?,?,?,?,?)";
		util.update(sql, c.getUserId(), c.getUnitPrice(), c.getCommodityName(), c.getCommodityStock(),
				c.getCommodityPicture(), c.getProductInformation());
		return c;
	}

	@Override
	public List<Commodity> delete(int id) {
		util.update("delete from xyw_commodity where Commodity_id=?", id);
		return null;
	}

	@Override
	public Commodity update(Commodity c) {
		util.update(
				"update xyw_commodity set User_id=?,Unit_Price=?,Commodity_name=?,Commodity_stock=?,Commodity_picture=?,Product_information=?  where  Commodity_id=?",
				c.getUserId(), c.getUnitPrice(), c.getCommodityName(), c.getCommodityStock(), c.getCommodityPicture(),
				c.getProductInformation(),c.getCommodityId());
		return c;

	}

	@Override
	public Commodity queryById(int id) {
		CachedRowSet rs = util.query("select * from xyw_commodity  where Commodity_id=?", id);

		UserDao userdao = new UserDaoImpl();
		try {
			if (rs.next()) {
				Commodity d = new Commodity();
				d.setCommodityId(rs.getInt("Commodity_id"));
				d.setUnitPrice(rs.getInt("Unit_Price"));
				d.setCommodityName(rs.getString("Commodity_name"));
				d.setUserId(userdao.queryByID(rs.getInt("User_id")));
				d.setCommodityStock(rs.getInt("Commodity_stock"));
				d.setCommodityPicture(rs.getString("Commodity_picture"));
				d.setProductInformation(rs.getString("Product_information"));
				return d;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	@Override
	public Commodity queryByName(String name) {
		CachedRowSet rs = util.query("select * from xyw_commodity  where Commodity_name=?", name);

		UserDao userdao = new UserDaoImpl();
		try {
			if (rs.next()) {
				Commodity d = new Commodity();
				d.setCommodityId(rs.getInt("Commodity_id"));
				d.setUnitPrice(rs.getInt("Unit_Price"));
				d.setCommodityName(rs.getString("Commodity_name"));
				d.setUserId(userdao.queryByID(rs.getInt("User_id")));
				d.setCommodityStock(rs.getInt("Commodity_stock"));
				d.setCommodityPicture(rs.getString("Commodity_picture"));
				d.setProductInformation(rs.getString("Product_information"));
				return d;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	@Override
	public int getCount() {
		CachedRowSet rs = util.query("select count(Commodity_id) as num from xyw_commodity");
		try {
			if (rs.next()) {
				return rs.getInt("num");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			util.closeCacheRowSet(rs);
		}
		return 0;
	}

	@Override
	public List<Commodity> getPage(int page, int size) {
		List<Commodity> list = new ArrayList<>();
		CachedRowSet rs = util.query("select * from xyw_commodity  ORDER  BY  Commodity_id  DESC  limit  " + (page - 1) * size +  " ,  "  + size);
		UserDao userdao=new UserDaoImpl();
		try {
			while (rs.next()) {
				Commodity d=new Commodity();
				d.setCommodityId(rs.getInt("Commodity_id"));
				d.setUnitPrice(rs.getInt("Unit_Price"));
				d.setCommodityName(rs.getString("Commodity_name"));
				d.setUserId(userdao.queryByID(rs.getInt("User_id")));
				d.setCommodityStock(rs.getInt("Commodity_stock"));
				d.setCommodityPicture(rs.getString("Commodity_picture"));
				d.setProductInformation(rs.getString("Product_information"));
				list.add(d);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}

	@Override
	public boolean deleteById(int id) {
		
		return util.update("delete from xyw_commodity where Commodity_id=?", id);
	}

}
